﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

namespace PulsatrixDummy.Services
{
    public class DummyService
    {
        private static SqlConnection Connection;

        #region SqlCommands
        private static string GetSubscriberByEmail = "select * from [dbo].[Subscriber] where [Email] = ";
        private static string InsertSubscriber = "insert into [dbo].[Subscriber] (Name, Email, Notes, SendingDate) values ";
        #endregion

        #region Singleton
        private static DummyService instance;
        private DummyService()
        {
            string connection = ConfigurationManager.ConnectionStrings["PulsatrixDummyDb"].ConnectionString;
            string connectionString = connection.Substring(connection.IndexOf("data source"), connection.IndexOf("App=EntityFramework") - connection.IndexOf("data source"));
            Connection = new SqlConnection(connectionString);
        }
        public static DummyService Instance
        {
            get
            {
                if (instance == null)
                    instance = new DummyService();
                return instance;
            }
        }
        #endregion

        private SqlDataReader RunQuery(string query)
        {
            SqlCommand command = new SqlCommand(query);
            if (Connection.State != System.Data.ConnectionState.Open)
                Connection.Open();

            command.Connection = Connection;
            return command.ExecuteReader();
        }

        public bool SubscriberExists(string email)
        {
            var reader = RunQuery(GetSubscriberByEmail + "'" + email + "'");
            while (reader.Read()) return true;
            return false;
        }
        public bool AddSubscriber(string name, string email, string notes)
        {
            if (email == null || email == string.Empty)
                return false;

            if (!SubscriberExists(email)) // add new
                RunQuery(InsertSubscriber + "('" + name + "', '" + email + "', '" + notes + "', '" + DateTime.UtcNow.ToString() + "')");
            else // concat notes & change date
            {
                string query = "update [dbo].[Subscriber] set ";

                if (name != null && name != string.Empty)
                    query += "Name='" + name + "', ";
                query += "Notes= cast (Notes as nvarchar(MAX)) + cast ('" + notes + "' as nvarchar(MAX)) ";
                query += "where Email='" + email + "'";
                RunQuery(query);
            }
            return true;
        }
    }
}